let assignSQLMap = new Map()
assignSQLMap.set("insertMissionAssign"," INSERT INTO MissionManagermentSystem.staff_mission_assign (staff_id, mission_id, status, submit_date) VALUES (?, ?, 0, null)")
assignSQLMap.set("deleteMissionAssign","DELETE FROM MissionManagermentSystem.staff_mission_assign WHERE staff_id = ? AND mission_id = ?")
assignSQLMap.set("selectMyTodayMission","select mission_collection.id as mission_id,content,priority,mission_name,DATE_FORMAT(deadline,'%Y-%m-%d %H:%i:%s') as deadline,sma.status from\n" +
    "              mission_collection\n" +
    "inner  join staff_mission_assign sma on mission_collection.id = sma.mission_id\n" +
    "inner  join staff s on sma.staff_id = s.id\n" +
    "where  TIMESTAMPDIFF(SECOND,Date(now()),deadline)<86400 && TIMESTAMPDIFF(SECOND,Date(now()),deadline)>=0 && staff_id=?")
assignSQLMap.set("selectMyTomorrowMission","select mission_collection.id as mission_id,content,priority,mission_name,DATE_FORMAT(deadline,'%Y-%m-%d %H:%i:%s') as deadline,sma.status from\n" +
    "              mission_collection\n" +
    "inner  join staff_mission_assign sma on mission_collection.id = sma.mission_id\n" +
    "inner  join staff s on sma.staff_id = s.id\n" +
    "where  TIMESTAMPDIFF(SECOND,Date(now()),deadline)>86400 && TIMESTAMPDIFF(SECOND,Date(now()),deadline)<=172000 && staff_id=?")
assignSQLMap.set("selectAssignMission","select mission_collection.id as mission_id,s.id as staff_id,name as staff_name,email,content,priority,mission_name,DATE_FORMAT(deadline,'%Y-%m-%d %H:%i:%s') as deadline,sma.status from\n" +
    "              mission_collection\n" +
    "inner  join staff_mission_assign sma on mission_collection.id = sma.mission_id\n" +
    "inner  join staff s on sma.staff_id = s.id")
assignSQLMap.set("selectAssignMissionByStaffId","select mission_collection.id as mission_id,content,priority,mission_name,DATE_FORMAT(deadline,'%Y-%m-%d %H:%i:%s') as deadline,sma.status from\n" +
    "              mission_collection\n" +
    "inner  join staff_mission_assign sma on mission_collection.id = sma.mission_id\n" +
    "inner  join staff s on sma.staff_id = s.id" +
    " where s.id = ?")
assignSQLMap.set("selectUnAssignMission","select mission_collection.id as mission_id,content,priority,mission_name,DATE_FORMAT(deadline,'%Y-%m-%d %H:%i:%s') as deadline,sma.status from\n" +
    "mission_collection\n" +
    "left  join staff_mission_assign sma on mission_collection.id = sma.mission_id\n" +
    "left  join staff s on sma.staff_id = s.id\n" +
    "where isnull(sma.staff_id)"
)


assignSQLMap.set("updateAssignMission","UPDATE MissionManagermentSystem.staff_mission_assign t SET t.status = 1 WHERE t.staff_id = ? AND t.mission_id = ?")

module.exports=assignSQLMap